-- managed lock priority for online operations

-- query window 1
USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
	SELECT
		BusinessEntityID,
		AccountNumber,
		Name,
		CreditRating
	FROM Purchasing.Vendor
	WHERE PreferredVendorStatus = 1 AND ActiveFlag = 1;
	
	SELECT
		resource_type,
		request_mode,
		resource_description,
		resource_associated_entity_id,
		request_status
	FROM sys.dm_tran_locks WHERE request_session_id = @@SPID;

 	WAITFOR DELAY '00:02:00';
COMMIT TRANSACTION

-- query window 2
USE AdventureWorks;
GO

-- online rebuild the AK_Vendor_AccountNumber index
ALTER INDEX AK_Vendor_AccountNumber
ON Purchasing.Vendor REBUILD
-- Wait 0 minutes (execute immediately) and kill all blockers.
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes,
	ABORT_AFTER_WAIT = BLOCKERS)));

-- online rebuild the AK_Vendor_AccountNumber index again
ALTER INDEX AK_Vendor_AccountNumber
ON Purchasing.Vendor REBUILD
-- Wait for up to 1 minutes to execute.
-- If the Sch-M lock cannot be granted within this timeframe, terminate the DDL statement.
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes,
	ABORT_AFTER_WAIT = SELF)));